#!/usr/bin/env node

const { Client } = require('pg');
const sqlite3 = require('sqlite3').verbose();
const path = require('path');

// PostgreSQL connection configuration
const pgConfig = {
  host: 'localhost',
  port: 15432,
  user: 'postgres',
  password: 'qwe!@#',
  database: 'Eng'
};

// SQLite database path
const sqliteDbPath = path.join(__dirname, '../resources/db/db.database');

async function syncEnwordsByLetter() {
  console.log('Starting synchronization of enwords by letter...');
  
  // Connect to PostgreSQL
  const pgClient = new Client(pgConfig);
  
  try {
    await pgClient.connect();
    console.log('Connected to PostgreSQL database');
    
    // Query all words from enwords table
    const res = await pgClient.query('SELECT word FROM public.enwords');
    console.log(`Found ${res.rows.length} words in enwords table`);
    
    // Connect to SQLite database
    const sqliteDb = new sqlite3.Database(sqliteDbPath);
    
    // Group words by first letter
    const wordsByLetter = {};
    
    res.rows.forEach(row => {
      // Skip rows with null or empty words
      if (!row.word || row.word.trim() === '') {
        return;
      }
      
      // Get first letter and convert to lowercase
      const firstLetter = row.word.trim().charAt(0).toLowerCase();
      
      // Only process alphabetic characters
      if (firstLetter >= 'a' && firstLetter <= 'z') {
        if (!wordsByLetter[firstLetter]) {
          wordsByLetter[firstLetter] = [];
        }
        wordsByLetter[firstLetter].push(row.word.trim());
      }
    });
    
    // Create tables and insert words for each letter
    sqliteDb.serialize(() => {
      // Drop existing tables
      Object.keys(wordsByLetter).forEach(letter => {
        const tableName = `enwords_${letter}`;
        sqliteDb.run(`DROP TABLE IF EXISTS ${tableName}`);
      });
      
      // Create tables and insert words for each letter
      Object.keys(wordsByLetter).forEach(letter => {
        const tableName = `enwords_${letter}`;
        
        // Create table for this letter
        sqliteDb.run(`CREATE TABLE ${tableName} (
          id INTEGER PRIMARY KEY AUTOINCREMENT,
          word TEXT UNIQUE NOT NULL
        )`);
        
        // Prepare statement for inserting words
        const stmt = sqliteDb.prepare(`INSERT INTO ${tableName} (word) VALUES (?)`);
        
        // Insert words
        let insertedCount = 0;
        wordsByLetter[letter].forEach(word => {
          stmt.run(word);
          insertedCount++;
        });
        
        stmt.finalize();
        console.log(`Created table ${tableName} with ${insertedCount} words`);
      });
      
      // Create a view that combines all letter-specific tables
      const unionQueries = Object.keys(wordsByLetter)
        .map(letter => `SELECT word FROM enwords_${letter}`)
        .join(' UNION ALL ');
      
      if (unionQueries) {
        sqliteDb.run(`DROP VIEW IF EXISTS enwords`);
        sqliteDb.run(`CREATE VIEW enwords AS ${unionQueries}`);
        console.log('Created view "enwords" combining all letter-specific tables');
      }
    });
    
    sqliteDb.close();
    console.log('Successfully synchronized all words by letter');
    
  } catch (err) {
    console.error('Error during synchronization:', err);
  } finally {
    await pgClient.end();
    console.log('Disconnected from PostgreSQL database');
  }
}

// Run the sync function
syncEnwordsByLetter();